|
Installation note - SQL Database Scripts |
|
To conduct the SQL 2000 course, the
following databases need to be installed:
|
|
| Note: Before you run these script, you need to edit the scripts and change the server name to the name of the machine on which SQL Server is installed. You should back up these databases so that they can be restored later. | |
| A brief description of the various scripts and their content is given below. | |
| Scripts | Description |
| CreateServerLogins.bat | This script creates 30 SQL Server logins for students. You need to edit the script and change the server name to the name of the machine on which SQL Server is installed. Running this script is a one-time activity. |
| InstallRecruitmentDB.bat | This script creates the Recruitment database and its objects. You must back up the database after creating it. |
| InstallGlobalToyzDB.bat | This script creates the GlobalToyz database and its objects. You must back up the database after creating it. |
| InstallRecruitmentDBForLesson5.bat | This script is to be used for Lesson 5. |
| INSTALLRECRUITMENTDBWITHOUTCONSTRAINT.BAT | This script creates the Recruitment database without any constraints. You need to execute this script for demonstrations and practices of Lesson 6, 7, 8, 9, 10, 11, 12, 13 and 14. |
| INSTALLGLOBALTOYZDBWITHOUTCONSTRAINTS.BAT | This script creates the GlobalToyz database without any constraints. You need to execute this script for unguided practice 4, 5, 6, 7, 8 and 9 |
| Before every OCR and Unguided Practice session,
the faculty needs to drop the database and restore the backed up
copy using the Enterprise Manager of Microsoft SQL Server. Note: You can also reinstall the database but, it takes less time to restore a database than to installing it. |
|
Frequently asked questions about scripts:
Q) Which script needs to be executed first?
Answer) You need to execute the CREATESQLSERVERLOGINS.BAT before executing any other script. The contents of the script are:
osql -Usa -Psasa -Svandanak-f90 -iCreateSQLServerLogins.sql
You need to modify the script to be able to execute it on your machine. For example if the name of the server on which SQL Server installed is NIIT-RG-MR and the sa login does not have any password, you need to change the command to:
osql -Usa -P -SNIIT-RG-MR -iCreateSQLServerLogins.sql
Save the changes and execute the batch file. This batch-file would call the CreateSQLServerLogins script, which in turn would use the sp_addlogin username, password command to add user logins to SQL Server. It would create 30 users with the names user1, user2, user3 ... user30. It would also create a faculty login. You should ask students to use one of these logins to connect to the SQL Server. Do not let them use the faculty or the sa login.
This script needs to be executed only after the installation of the SQL server since these logins are used to connect to the SQL Server.
Q) How should I create the Recruitment database?
Answer) To create the Recruitment database, you can use the following scripts:
INSTALLRECRUITMENTDB.BAT. Its contents are:
osql -Usa -Psasa -SVANDANAK-f90 -iCreateRecruitmentDb.sql |
You need to modify the script to be able to execute the script. For example, If the name of your server is NIIT-RG-MR and the sa login does not have any password, you need to modify the script to:
osql -Usa -P -SNIIT-RG-MR -iCreateRecruitmentDb.sql |
In case sa login of NIIT-RG-MR has a password say secret, you need to modify the script as follows:
osql -Usa -Psecret -SNIIT-RG-MR -iCreateRecruitmentDb.sql |
Important: even though sa has password as secret the last two lines do not have any passwords. They just contain -P. This is because these would create the schema for all the users individually.
Q) What is the purpose and sequence of executing these scripts?
Answer) If you need to install databases with constraints, you should execute these scripts in the following sequence:
1) CREATESQLSERVERLOGINS.BAT
2) INSTALLRECRUITMENTDB.BAT
3) INSTALLGLOBALTOYZDB.BAT
. In case you are working on Lesson 6 onwards, you need to execute the following scripts after having execute the CREATESQLSERVERLOGINS.BAT once.
4) INSTALLRECRUITMENTDBWITHOUTCONSTRAINT.BAT
5) INSTALLGLOBALTOYZDBWITHOUTCONSTRAINTS.BAT
Q) Why should I execute the batch file and not directly execute the sql scripts?
Answer) The batch file uses a for loop to create 30 copies of the same tables. If you execute the script by loading it in the Query analyzer, you would just get one copy of the tables. Thirty copies are required as all the users should to have there own copy of the tables. In case you don't create 30 copies, if one person drops a table, the other person would not be able to use that table.
Q) How do I execute the .BAT files?
Answer) You can double click the batch files execute them. You can also access the command prompt, and type the name of a batch file on the command prompt, and then press Enter to execute it.
Q) What is the difference between the three batch files:
INSTALLRECRUITMENTDB.BAT
INSTALLRECRUITMENTDBFORLESSON5.BAT
and INSTALLRECRUITMENTDBWITHOUTCONSTRAINT.BAT
Answer) INSTALLRECRUITMENTDB.BAT: would create the Recruitment database with constraints. All the tables will be created with primary key and foreign key relation.
INSTALLRECRUITMENTDBFORLESSON5.BAT: would drop the existing recruitment database and recreate it without any objects. This is required because we are creating tables in lesson 5. If the tables already exists, an error would be generated.
INSTALLRECRUITMENTDBWITHOUTCONSTRAINT.BAT: would drop the existing recruitment database and recreate it without any constraints. All the tables will be created but they would not have primary key and foreign key relations. Any table can be dropped. Also, you can create indexes and constrains on the existing tables.
Q) What is the difference between INSTALLGLOBALTOYZDB.BAT and INSTALLGLOBALTOYZDBWITHOUTCONSTRAINTS.BAT files?
Answer)INSTALLGLOBALTOYZDB.BAT: would create the GlobalToyz database with constrains. You would not be able to drop any table as primary key and foreign key relations would exist.
INSTALLGLOBALTOYZDBWITHOUTCONSTRAINTS.BAT: would create the GlobalToyz database without any constrains. You can drop any table if the database is created using this batch file.
Q) What is the use of the following SQL scripts:
CREATEUSERSFORRECRUITMENTDB.SQL
CREATERECRUITMENTOBJECTS.SQL
CREATERECRUITMENTOBJECTSWITHOUTCONSTRAINT.SQL
CREATERECRUITMENTDB.SQL
Answer) CREATEUSERSFORRECRUITMENTDB.SQL : Assigns permissions to users to use the database.
CREATERECRUITMENTOBJECTS.SQL: Creates the Recruitment database objects and adds records in the database. The tables created contain constraints.
CREATERECRUITMENTOBJECTSWITHOUTCONSTRAINT.SQL: Creates Recruitment database objects and records in the database. The tables created do not contain constraints.
CREATERECRUITMENTDB.SQL: Creates a blank Recruitment database. It does not create any database objects.
Q) What is the use of the following sql scripts:
CREATEUSERSFORGLOBALTOYZDB.SQL
CREATEGLOBALTOYZOBJECTSWITHOUTCONSTRAINTS.SQL
CREATEGLOBALTOYZOBJECTS.SQL
CREATEGLOBALTOYZDB.SQL
Answer)
CREATEUSERSFORGLOBALTOYZDB.SQL: Assigns permission to users to use the GlobalToyz database.
CREATEGLOBALTOYZOBJECTSWITHOUTCONSTRAINTS.SQL: Creates the objects in GlobalToyz database without any constraints. Tables in the database can be dropped.
CREATEGLOBALTOYZOBJECTS.SQL: Creates the objects in GlobalToyz database with constraints. Tables in the database cannot be dropped.
CREATEGLOBALTOYZDB.SQL: Creates a blank GlobalToyz database after dropping any existing GlobalToyz database.
Q) What is present in the SQL files?
Answer) The SQL files contain SQL statements to create the Recruitment and the GlobalToyz database.
Q) On which machine should you execute these scripts?
Answer) You should execute these scripts on the machine where the SQL Server is installed.
Q) Do I have to execute the script on all the machines?
Answer) No, you just need to install the script on one machine and other persons can use the Query Analyzer to connect to the SQL server and use the database. You need to install SQL Server on one machine and the Client components on rest of the machines. Only in case you have problems in networking and are not able to connect to the SQL server and the machine configuration is such that you can install SQL Server on all the machines, should you execute the script on all the machines.
Q) How should I back up and restore the database?
Answer) To back up the database:
1) Invoke Enterprise Manager.
2) Right-Click the database to be backed up.
3) Select All Tasks.
4) Select the backup option.
The following figure would be displayed:
5) Click the Add button.
6) Enter the name of the backup file.
7) Click OK to start the backup.
To restore the data from the backup:
1) Delete the existing database.
2) Create a database with the same name that existed on the server previously.
3) Right-Click the database to be restored.
4) Select All Tasks.
5) Select the restore database option.
Following dialog box would be displayed:
6) Click the From device option.
7) Click the Select Devices button to open the Choose Restore Device dialog box.
8) In the dialog box, select the name of the file that you used while creating the backup.
9) Click OK to return to the Restore database dialog box.
10) Click OK to start restoring the database.
Q) How do I change the password of sa or faculty login?
Answer) You can change the password of any user using the sp_password command. The syntax of the command is:
sp_password old_password, new_password, login_name
Example
sp_password null,'newpassword','faculty'
Q) If one user say, user1, drops a table, say Employee, then can another user, say user2, drop the same table?
Answer) Yes, if you execute the batch files in the proper sequence. This is possible as each table is referenced by servername.databasename.username.tablename. For Example, if you have a table called Employee in the Recruitment database on the outlookserver created by user1, you can reference it as outlookserver.recruitment.user1.employee. When you execute the batch file, 30 copies of the schema are created. Each user has his/her own copy.
Q) How do I give a demonstration of locking? Do I need two machines to demonstrate locking?
Answer) No, you do not need two machines to give a demonstration of locking, You can open two connections to the SQL server on the same machine and then you can resize the two windows so that both are visible. Ask the students to assume that the first window is one computer and the second window is another computer.
Q) Can students use the Enterprise Manager without using the sa login?
Answer) Yes, students should use logins user1, user2, user3... etc. to connect to the SQL server from the Enterprise Manager. To change a username while connecting to the server, right click the SQL Server and click the Edit SQL Server Properties option. In the dialog box, enter the new user name with which you want to connect to the SQL server.
Q) What if the tables in a database is deleted by a student?
Answer) In case the tables are deleted, you can restore them from the backup or execute the scripts to recreate the database.
Q) What should I do if the table that needs to be created is already present in the database?
Answer) In case you have executed CREATERECRUITMENTDB.SQL or CREATEGLOBALTOYZDB.SQL, you would not be able to drop any tables without removing the constraints. In case you have executed CREATEGLOBALTOYZOBJECTSWITHOUTCONSTRAINTS.SQL or CREATERECRUITMENTOBJECTSWITHOUTCONSTRAINT.SQL you can drop any table as the database does not contain any constraints.